<?php
class Khachhang_Model_Taiquanly extends Zend_Db_Table{
	
	protected $_name = 'khachhang';
	protected $_primary = 'id';
	
	protected $_ids;
	
	public function getItem($arrParam = null, $options = null) {
		
		if ($options['task'] == 'admin-info' || $options['task'] == 'admin-edit') {
			$db = Zend_Registry::get('connectDb');
 			//$db = Zend_Db::factory($adapter, $config);`
			$select = $db	->	select()
							->	from('khachhang AS kh')
							->	joinLeft('khachhang_nhom AS khn', 'kh.id_cat = khn.id', array('khn.name AS nhom_name', 'khn.id AS id_nhom'))
							->	where('kh.id = ?', $arrParam['id_kh']);
			$result = $db	->	fetchRow($select);
		}
		
		return $result;
	}
	
	public function countItem($arrParam = null, $options = null) {
		$ssFilter  = $arrParam['ssFilter'];
		$db = Zend_Registry::get('connectDb');
		//$db = Zend_Db::factory($adapter, $config);
		
		$quyen_nv = new Zendvn_System_Info();
		$quyen_nhom = $quyen_nv->getGroupInfo('permission');
		$quyen_info = $quyen_nv->getInfo();
		$quyen_member = $quyen_nv->getMemberInfo();
		$quyen_phongban = $quyen_nv->getPhongbanInfo();
		
		if ($options == null) {
			$select = $db	->	select()
							->	from('khachhang AS kh', array('COUNT(kh.id) AS totalItem'))
							->	join('nam_hopdong AS hd', 'hd.id_kh = kh.id')
							->	where('kh.status != ?', -1);
			$result = $db->fetchOne($select);
		}
		
		if ($options['task'] == 'admin-list') {
			$select = $db	->	select()
							->	from('khachhang AS kh', array('*', "DATE_FORMAT(kh.created, '%d/%m/%Y') AS created"))
							->	joinLeft('khachhang_nhom AS khn', 'khn.id = kh.id_cat', array('khn.name AS category_name'))
							->	joinLeft('users AS u', 'u.id = kh.id_nhanvien', array('u.name AS quanly_name', 'u.alias AS quanly_alias'))
							->	joinLeft('phongban AS pb', 'pb.id = u.id_phongban', array('pb.name AS phongban_name'))
							->	where('kh.status != ?', -1)
							->	where('kh.id_clb = ?', '0')
							->	where('kh.status_khachhang = ?', 0)
							->	group('kh.id');
			
			if (!empty($ssFilter['keywords'])) {
				$select->where("(kh.name LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.id = '" . $ssFilter['keywords'] . "') OR (kh.code LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.email LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.phone LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.telephone LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.fax LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.address LIKE '%" . $ssFilter['keywords'] . "%')");
			}
			
			if (!empty($ssFilter['id_phongban'])) {
				$select_category = $db 	-> select()
										-> from('phongban',array('id','name','parents'))
										-> where('status = ?',1);
				$resultCategory = $db->fetchAll($select_category);

				$system = new Zendvn_System_Recursive($resultCategory);
				$newArray = $system->builArray($ssFilter['id_phongban']);
				$tmp[] = $ssFilter['id_phongban'];
				if (count($newArray)>0) {
					foreach ($newArray as $key => $val) {
						$tmp[] = $val['id'];
					}
				}
				$ids = implode(',', $tmp);
				
				$selectUser	= $db	->	select()
									->	from('users AS u', array('id'))
									->	where('u.id_phongban IN(' . $ids . ')');
				$resultUser = $db	->	fetchCol($selectUser);
					
				if(count($resultUser) > 0) {
					$idUser = implode(',', $resultUser);
				} else {
					$idUser = 0;
				}
				
				$select->where('kh.id_nhanvien IN(' . $idUser . ')');
			} else {
				if($quyen_nhom == 'Limit Access') {
					$select_category = $db	->	select()
											->	from('phongban AS pb', array('id','name','parents'));
					
					$resultCategory = $db	->	fetchAll($select_category);
					
					$system = new Zendvn_System_Recursive($resultCategory);
					$newArray = $system->builArray($quyen_phongban['id']);
					$tmp[] = $quyen_phongban['id'];
					if (count($newArray)>0) {
						foreach ($newArray as $key => $val) {
							$tmp[] = $val['id'];
						}
					}
					$ids = implode(',', $tmp);
					
					$selectUser	= $db	->	select()
										->	from('users AS u', array('id'))
										->	where('u.id_phongban IN(' . $ids . ')');
					$resultUser = $db	->	fetchCol($selectUser);
					
					if(count($resultUser) > 0) {
						$idUser = implode(',', $resultUser);
					} else {
						$idUser = 0;
					}
					
					$select->where('kh.id_nhanvien IN(' . $idUser . ')');
				}
			}
				
			if (!empty($ssFilter['id_nhanvien'])) {
				$select->where('kh.id_nhanvien = ?', $ssFilter['id_nhanvien']);
			}
			
			if (!empty($ssFilter['blocks'])) {
				
				//$ssFilter['blocks'];
				$date = New Zend_Date();
				$ngay = $date->subDay($ssFilter['blocks'])->toString('YYYY-MM-dd');
				$select 	->	where('kh.created <= ?', $ngay);
			}
			
			$result = $db->fetchAll($select);
		}
		
		return $result;
	}
	
	public function itemInSelectbox($arrParam = null, $options = null) {
		$db = Zend_Registry::get('connectDb');
		//$db = Zend_Db::factory($adapter, $config);
		if ($options == null) {
			$select = $db	->	select()
							->	from('khachhang_nhom AS khn', array('id', 'name'))
							->	order('khn.order ASC')
							->	order('khn.id ASC');
			$result = $db	->	fetchPairs($select);
			
			$result[0] = '- Lựa chọn -';
			ksort($result);
		}
	
		return $result;
	}
	
	public function listItem($arrParam = null, $options = null) {
		$db = Zend_Registry::get('connectDb');
		//$db = Zend_Db::factory($adapter, $config);
		$ssFilter  = $arrParam['ssFilter'];
		
		$quyen_nv = new Zendvn_System_Info();
		$quyen_nhom = $quyen_nv->getGroupInfo('permission');
		$quyen_info = $quyen_nv->getInfo();
		$quyen_member = $quyen_nv->getMemberInfo();
		$quyen_phongban = $quyen_nv->getPhongbanInfo();
		
		if ($options['task'] == 'admin-list') {
			
			$paginator = $arrParam['paginator'];

			$select = $db	->	select()
							->	from('khachhang AS kh', array('*', "DATE_FORMAT(kh.created, '%d/%m/%Y') AS created"))
							->	joinLeft('khachhang_nhom AS khn', 'khn.id = kh.id_cat', array('khn.name AS category_name'))
							->	joinLeft('users AS u', 'u.id = kh.id_nhanvien', array('u.name AS quanly_name', 'u.alias AS quanly_alias'))
							->	joinLeft('phongban AS pb', 'pb.id = u.id_phongban', array('pb.name AS phongban_name'))
							->	where('kh.status != ?', -1)
							->	where('kh.id_clb = ?', '0')
							->	where('kh.status_khachhang = ?', 0)
							->	group('kh.id');

			if (!empty($ssFilter['col']) && !empty($ssFilter['order'])) {
				$select->order($ssFilter['col'] . ' ' . $ssFilter['order']);
			}
		
			if ($paginator['itemCountPerPage'] > 0) {
				$page = $paginator['currentPage'];
				$rowCount = $paginator['itemCountPerPage'];
				$select->limitPage($page, $rowCount);
			}
		
			if (!empty($ssFilter['keywords'])) {
				$select->where("(kh.name LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.id = '" . $ssFilter['keywords'] . "') OR (kh.code LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.email LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.phone LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.telephone LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.fax LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.address LIKE '%" . $ssFilter['keywords'] . "%')");
			}
				
			if ($ssFilter['id_cat'] > 0) {
				$select->where('kh.id_cat = ?', $ssFilter['id_cat']);
			}
			
			if (!empty($ssFilter['id_phongban'])) {
				$select_category = $db 	-> select()
										-> from('phongban',array('id','name','parents'))
										-> where('status = ?',1);
				$resultCategory = $db->fetchAll($select_category);

				$system = new Zendvn_System_Recursive($resultCategory);
				$newArray = $system->builArray($ssFilter['id_phongban']);
				$tmp[] = $ssFilter['id_phongban'];
				if (count($newArray)>0) {
					foreach ($newArray as $key => $val) {
						$tmp[] = $val['id'];
					}
				}
				$ids = implode(',', $tmp);
				
				$selectUser	= $db	->	select()
									->	from('users AS u', array('id'))
									->	where('u.id_phongban IN(' . $ids . ')');
				$resultUser = $db	->	fetchCol($selectUser);
					
				if(count($resultUser) > 0) {
					$idUser = implode(',', $resultUser);
				} else {
					$idUser = 0;
				}
				
				$select->where('kh.id_nhanvien IN(' . $idUser . ')');
			} else {
				if($quyen_nhom == 'Limit Access') {
					$select_category = $db	->	select()
											->	from('phongban AS pb', array('id','name','parents'));
					
					$resultCategory = $db	->	fetchAll($select_category);
					
					$system = new Zendvn_System_Recursive($resultCategory);
					$newArray = $system->builArray($quyen_phongban['id']);
					$tmp[] = $quyen_phongban['id'];
					if (count($newArray)>0) {
						foreach ($newArray as $key => $val) {
							$tmp[] = $val['id'];
						}
					}
					$ids = implode(',', $tmp);
					
					$selectUser	= $db	->	select()
										->	from('users AS u', array('id'))
										->	where('u.id_phongban IN(' . $ids . ')');
					$resultUser = $db	->	fetchCol($selectUser);
					
					if(count($resultUser) > 0) {
						$idUser = implode(',', $resultUser);
					} else {
						$idUser = 0;
					}
					
					$select->where('kh.id_nhanvien IN(' . $idUser . ')');
				}
			}
			
			if (!empty($ssFilter['id_nhanvien'])) {
				$select->where('kh.id_nhanvien = ?', $ssFilter['id_nhanvien']);
			}
			
			if (!empty($ssFilter['blocks'])) {
				$date = New Zend_Date();
				$ngay = $date->subDay($ssFilter['blocks'])->toString('YYYY-MM-dd');
				
				$select 	->	where('kh.created <= ?', $ngay);
			}
		
			$result = $db->fetchAll($select);
		}
		
		
		
		if ($options['task'] == 'admin-excel') {
				
			$paginator = $arrParam['paginator'];
		
			$select = $db	->	select()
							->	from('khachhang AS kh', array('*', "DATE_FORMAT(kh.created, '%d/%m/%Y') AS created"))
							->	joinLeft('khachhang_nhom AS khn', 'khn.id = kh.id_cat', array('khn.name AS category_name'))
							->	joinLeft('users AS u', 'u.id = kh.id_nhanvien', array('u.name AS quanly_name', 'u.alias AS quanly_alias'))
							->	joinLeft('phongban AS pb', 'pb.id = u.id_phongban', array('pb.name AS phongban_name'))
							->	where('kh.status != ?', -1)
							->	where('kh.id_clb = ?', '0')
							->	where('kh.status_khachhang = ?', 0)
							->	group('kh.id');
			//echo $select;die;
			if (!empty($ssFilter['col']) && !empty($ssFilter['order'])) {
				$select->order($ssFilter['col'] . ' ' . $ssFilter['order']);
			}
		
			if ($paginator['itemCountPerPage'] > 0) {
				$page = $paginator['currentPage'];
				$rowCount = $paginator['itemCountPerPage'];
				$select->limitPage($page, $rowCount);
			}
		
			if (!empty($ssFilter['keywords'])) {
				$select->where("(kh.name LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.id = '" . $ssFilter['keywords'] . "') OR (kh.code LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.email LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.phone LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.telephone LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.fax LIKE '%" . $ssFilter['keywords'] . "%') OR (kh.address LIKE '%" . $ssFilter['keywords'] . "%')");
			}
		
			if ($ssFilter['id_cat'] > 0) {
				$select->where('kh.id_cat = ?', $ssFilter['id_cat']);
			}
				
			if (!empty($ssFilter['id_phongban'])) {
				$select_category = $db 	-> select()
				-> from('phongban',array('id','name','parents'))
				-> where('status = ?',1);
				$resultCategory = $db->fetchAll($select_category);
		
				$system = new Zendvn_System_Recursive($resultCategory);
				$newArray = $system->builArray($ssFilter['id_phongban']);
				$tmp[] = $ssFilter['id_phongban'];
				if (count($newArray)>0) {
					foreach ($newArray as $key => $val) {
						$tmp[] = $val['id'];
					}
				}
		
				$ids = implode(',', $tmp);
				$select->where('kh.id_phongban IN(' . $ids . ')');
			}
				
			if (!empty($ssFilter['id_nhanvien'])) {
				$select->where('kh.id_nhanvien = ?', $ssFilter['id_nhanvien']);
			}
				
			if (!empty($ssFilter['blocks'])) {
				$date = New Zend_Date();
				$ngay = $date->subDay($ssFilter['blocks'])->toString('YYYY-MM-dd');
		
				$select 	->	where('kh.created <= ?', $ngay);
			}
			 
			$result = $db->fetchAll($select);
		}
		
		return $result;
	}

	public function deleteItem($arrParam = null, $options = null) {
		if ($options['task'] == 'admin-delete') {				
			$where = ' id = ' . $arrParam['id'];
			$data = array(
						'status' => -1
					);
			$result = $this->update($data, $where);
		}
	
		if ($options['task'] == 'admin-delete-muti') {
			$cid = explode(',', $arrParam['cid']);
			if (!empty($cid) && isset($arrParam['cid'])) {
				$ids = implode(',', $cid);
				$where = 'id IN (' . $ids . ')';
				$data = array(
						'status' => -1
				);
				$this->update($data, $where);
			}
	
		}
	}
}